﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using RCA1._0;
using System.Data;
using System.Data.OleDb;

namespace Royal_1._0_DAL_WS
{
    public class ChiNhanhDAO : DataProvider
    {
        
        public DataTable LayBang()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from CHINHANH";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        #region "Lấy đối tượng"
        private ChiNhanhDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();

            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();

            ChiNhanhDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO = new ChiNhanhDTO();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.IDChiNhanh = Convert.ToInt32(dr["IDChiNhanh"]);
                tempDTO.DiaChiChiNhanh = dr["DiaChiChiNhanh"].ToString();
                tempDTO.DienThoaiCN = dr["DienThoaiCN"].ToString();
                tempDTO.TinhTrangChiNhanh = Convert.ToInt32(dr["TinhTrangChiNhanh"]);
                              
            }
            cn.Close();
            return tempDTO;
        }
        #endregion


        #region "Lấy danh sách"
        private List<ChiNhanhDTO> LayDSach(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ChiNhanhDTO tempDTO;
            List<ChiNhanhDTO> dsCHINHANH = new List<ChiNhanhDTO>();
            while (dr.Read())
            {
                tempDTO = new ChiNhanhDTO();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.IDChiNhanh = Convert.ToInt32(dr["IDChiNhanh"]);
                tempDTO.DiaChiChiNhanh = dr["DiaChiChiNhanh"].ToString(); 
                tempDTO.DienThoaiCN = dr["DienThoaiCN"].ToString();
                tempDTO.TinhTrangChiNhanh = Convert.ToInt32(dr["TinhTrangChiNhanh"]);
                dsCHINHANH.Add(tempDTO);
            }
            return dsCHINHANH;
        }

        public List<ChiNhanhDTO> LayDanhSachTheoMaCongTy(int MaCongTy)
        {
            String sql = "Select * From CHINHANH Where TinhTrangChiNhanh = 1 and MaCongTy = " + MaCongTy;
            return LayDSach(sql);
        }
        #endregion


        #region "Tìm kiếm"
        public ChiNhanhDTO TimTheoMa(int MaChiNhanh)
        {

            string strSQL = "select * from CHINHANH Where IDChiNhanh=" + MaChiNhanh.ToString();
            return LayDoiTuong(strSQL);
        }
        #endregion


        #region "Thêm"
        public void Them(ChiNhanhDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "insert into CHINHANH(DiaChiChiNhanh, MaCongTy, DienThoaiCN, TinhTrangChiNhanh) values (@DiaChiChiNhanh, @MaCongTy, @DienThoaiCN, @TinhTrangChiNhanh)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            
            cmd.Parameters.Add("@DiaChiChiNhanh", OleDbType.LongVarWChar).Value = tempDTO.DiaChiChiNhanh;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
            cmd.Parameters.Add("@DienThoaiCN", OleDbType.LongVarWChar).Value = tempDTO.DienThoaiCN;
            cmd.Parameters.Add("@TinhTrangChiNhanh", OleDbType.Integer).Value = tempDTO.TinhTrangChiNhanh=1;
            
            
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


        #region "Xóa"
        //public void Xoa(int IDChiNhanh)
        //{
        //    OleDbConnection cn = this.Connect();
        //    string strSQL = "delete from CHINHANH Where IDChiNhanh = " + IDChiNhanh.ToString();
        //    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
        //    cmd.ExecuteNonQuery();
        //    cn.Close();
        //}

        public void Xoa(int MaChiNhanh)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update CHINHANH set TinhTrangChiNhanh = 2 where IDChiNhanh = " + MaChiNhanh.ToString();
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


        #region "Sửa"
        public void Sua(ChiNhanhDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update CHINHANH set DiaChiChiNhanh = @DiaChiChiNhanh, DienThoaiCN = @DienThoaiCN, MaCongTy = @MaCongTy, TinhTrangChiNhanh = @TinhTrangChiNhanh where IDChiNhanh = @IDChiNhanh";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@DiaChiChiNhanh", OleDbType.LongVarWChar).Value = tempDTO.DiaChiChiNhanh;
            cmd.Parameters.Add("@DienThoaiCN", OleDbType.LongVarWChar).Value = tempDTO.DienThoaiCN;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;            
            cmd.Parameters.Add("@TinhTrangChiNhanh", OleDbType.Integer).Value = tempDTO.TinhTrangChiNhanh;
            cmd.Parameters.Add("@IDChiNhanh", OleDbType.Integer).Value = tempDTO.IDChiNhanh;          
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion




    }
}